/**
 * 
 */
package database;

import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * @author Aubry Pirian
 * @version 0.5
 */
public class MySQLConnector extends DbConnector implements IDbConnector {

	/**
	 * Constructeur de MySQLConnector, avec port
	 * 
	 * @param address	: adresse du serveur
	 * @param port		: port du serveur
	 * @param userName	: nom d'utilisateur
	 * @param passwd	: mot de passe
	 * @param dbName	: nom de la base de donnees
	 * @throws NoSuchFieldException : attribut incorrect
	 */
	public MySQLConnector(String address, int port, String userName, String passwd, String dbName)
	throws NoSuchFieldException {
		if (address.length() == 0 || address == null)
			throw new NoSuchFieldException("Adresse vide");
		if (userName.length() == 0 || userName == null)
			throw new NoSuchFieldException("Nom d'utilisateur vide");
		if (dbName.length() == 0 || dbName == null)
			throw new NoSuchFieldException("Base de données non précisée.");
		if (port < 0)
			throw new NoSuchFieldException("Port incorrect.");
		this.address = address;
		this.port = port;
		this.userName = userName;
		this.passwd = passwd;
		this.dbName = dbName;
	}

	/**
	 * Constructeur de MySQLConnector, avec port par defaut
	 * 
	 * @param address	: adresse du serveur
	 * @param userName	: nom d'utilisateur
	 * @param passwd	: mot de passe
	 * @param dbName	: nom de la base de donnees
	 * @throws NoSuchFieldException : attribut non valide
	 */
	public MySQLConnector(String address, String userName, String passwd, String dbName) throws NoSuchFieldException {
		if (address.length() == 0 || address == null)
			throw new NoSuchFieldException("Adresse vide");
		if (userName.length() == 0 || userName == null)
			throw new NoSuchFieldException("Nom d'utilisateur vide");
		if (dbName.length() == 0 || dbName == null)
			throw new NoSuchFieldException("Base de données non précisée.");
		this.address = address;
		this.port = -1;
		this.userName = userName;
		this.passwd = passwd;
		this.dbName = dbName;
	}
	
	/**
	 * Methode retournant l'adresse complete de connexion a la base de donnees
	 */
	protected String getFullAddress () {
		return "jdbc:mysql://"+super.getFullAddress();
	}

	/**
	 * Methode retournant l'objet Connection d'acces a la base
	 * @throws ClassNotFoundException : impossible d'accéder au driver JDBC
	 * @throws SQLException
	 */
	public void connect() throws SQLException, ClassNotFoundException {
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			throw e;
		}
		// Getting connection through DriverManager
		/*System.out.println("Address : " + this.getFullAddress() + ", "
				+ this.userName + ", " + this.passwd);*/
		try {
			this.conn = DriverManager.getConnection(this.getFullAddress(),this.userName, this.passwd);
		} catch (SQLException e) {
			throw e;
		}
	}

	/**
	 * Methode creant la partie de la requete definissant les criteres de
	 * selection dans la BDD
	 * @param where	: clause a ecrire
	 * @return clause de condition sous forme de chaîne de caractères
	 */
	protected static String buildCondition(Clause cond) {
		return buildWhere(cond);
	}

	/**
	 * Methode creant la partie de la requete definissant les criteres de
	 * selection dans la BDD
	 * @param where	: clause a ecrire
	 * @return clause de condition MySQL sous forme de chaîne de caractères
	 */
	private static String buildWhere(Clause where) {
		return "WHERE " + where;
	}


	/**
	 * Méthode de création d'une table
	 * @param tableName	: nom de la table
	 * @param attrs		: attributs de la table
	 * @throws NoSuchFieldException : attribut non valide
	 * @throws SQLException
	 */
	public void createTable(String tableName, LinkedList<TypedAttribute> attrs)
			throws NoSuchFieldException,SQLException {
		if (tableName==null || tableName.length()==0 )
			throw new NoSuchFieldException("Nom de table non valide.");
		if (attrs == null)
			throw new NoSuchFieldException("Aucun attribut spécifié pour la table.");
		if (attrs.size() == 0)
			throw new NoSuchFieldException("Aucun attribut spécifié pour la table (liste vide).");
		Statement stmt;
		try {
			stmt = this.createStatement();
		} catch (SQLException e) {
			throw e;
		}
		String k, kk;
		LinkedList<String> usedKeys = new LinkedList<String>();
		String primaries = "";
		LinkedList<String> indexes = new LinkedList<String>();
		LinkedList<String> uniques = new LinkedList<String>();
		boolean autoIncrement=false;
		TypedAttribute ta;
		String query = "CREATE TABLE `" + tableName + "` (";
		Iterator<TypedAttribute> iterator = attrs.iterator();
		if (iterator.hasNext()) {
			ta = iterator.next();
			query += ta;
			if (ta.getKey()=='p') primaries += "`"+ta.getName()+"`,";
			if (ta.getKey()=='i') indexes.add(ta.getName());
			if (ta.getKey()=='u') uniques.add(ta.getName());
			if (ta.getAutoIncrement()) autoIncrement=true;
		}
		while (iterator.hasNext()) {
			ta = iterator.next();
			query += ", "+ta;
			if (ta.getKey()=='p') primaries += "`"+ta.getName()+"`,";
			if (ta.getKey()=='i') indexes.add(ta.getName());
			if (ta.getKey()=='u') uniques.add(ta.getName());
		}
		int l;
		l = primaries.length();
		if (l>0) query += ", PRIMARY KEY ("+primaries.substring(0,l-1)+")";
		Iterator<String> sIterator = indexes.iterator();
		while (sIterator.hasNext()) {
			k = sIterator.next();
			kk = k;
			while (usedKeys.indexOf(kk)>-1) kk+="_";
			query += ", KEY `"+kk+"` ("+k+")";
			usedKeys.add(kk);
		}
		sIterator = uniques.iterator();
		while (sIterator.hasNext()) {
			k = sIterator.next();
			kk = k;
			while (usedKeys.indexOf(kk)>-1) kk+="_";
			query += ", UNIQUE KEY `"+kk+"` ("+k+")";
			usedKeys.add(kk);
		}
		query += ")";
		if (autoIncrement) query += " AUTO_INCREMENT=0";
		query += ";";
		System.out.println("Requete SQL : " + query);
		try {
			stmt.executeUpdate(query);
		} catch (SQLException e) {
			throw e;
		}
	}


	/**
	 * Méthode de suppression d'une table
	 * @param tableName	: table à supprimer
	 * @throws SQLException
	 * @throws NoSuchFieldException	: attribut non valide
	 */
	public void dropTable(String tableName) throws SQLException, NoSuchFieldException {
		if (tableName==null || tableName.length()==0)
				throw new NoSuchFieldException("Nom de table non valide");
		Statement stmt;
		try {
			stmt = this.createStatement();
		} catch (SQLException e) {
			throw e;
		}
		String query = "DROP TABLE `" + tableName + "`;";
		System.out.println("Requete SQL : " + query);
		try {
			stmt.executeUpdate(query);
		} catch (SQLException e) {
			throw e;
		}
	}


	/**
	 * Méthode de vidage de table
	 * @param tableName	: table à vider
	 * @throws SQLException
	 * @throws NoSuchFieldException : attribut non valide
	 */
	public void emptyTable(String tableName) throws SQLException, NoSuchFieldException {
		if (tableName==null || tableName.length()==0)
			throw new NoSuchFieldException("Nom de table non valide");
		Statement stmt;
		try {
			stmt = this.createStatement();
		} catch (SQLException e) {
			throw e;
		}
		String query = "TRUNCATE TABLE `" + tableName + "`;";
		System.out.println("Requete SQL : " + query);
		try {
			stmt.executeUpdate(query);
		} catch (SQLException e) {
			throw e;
		}
	}


	/**
	 * Méthode d'insertion d'un enregistrement
	 * @param tableName	: table
	 * @param attrs		: liste d'attributs et valeurs
	 * @throws SQLException
	 * @throws NoSuchFieldException : attribut non valide
	 */
	public void insert(String tableName, LinkedList<ValuedAttribute> attrs) throws SQLException,NoSuchFieldException {
		if (tableName==null || tableName.length()==0)
			throw new NoSuchFieldException("Nom de table non valide");
		if ( attrs==null || attrs.size()==0 )
			throw new NoSuchFieldException("Liste d'attributs non valide");
		Statement stmt;
		try {
			stmt = this.createStatement();
		} catch (SQLException e) {
			throw e;
		}
		LinkedList<String> names = new LinkedList<String>();
		LinkedList<String> values = new LinkedList<String>();
		ValuedAttribute va;
		Iterator<ValuedAttribute> iterator = attrs.iterator();
		while (iterator.hasNext()) {
			va = iterator.next();
			names.add(va.getName());
			values.add(va.getValue());
		}
		String query = "INSERT INTO `" + tableName + "`";
		iterator = attrs.iterator();
		Iterator<String> sIterator = names.iterator();
		String namesQuery = "";
		while (sIterator.hasNext()) {
			namesQuery += "`" + names.poll() + "`,";
		}
		if (namesQuery.length() > 0) {
			query += " (" + namesQuery.substring(0, namesQuery.length() - 1)
					+ ")";
		}
		query += " VALUES (";
		sIterator = values.iterator();
		if (sIterator.hasNext()) {
			query += "'" + values.poll() + "'";
		}
		while (sIterator.hasNext()) {
			query += ",'" + values.poll() + "'";
		}
		query += ")";
		query += ";";
		System.out.println("Requete SQL : " + query);
		try {
			stmt.executeUpdate(query);
		} catch (SQLException e) {
			throw e;
		}
	}

	/**
	 * Méthode de selection
	 * @param tableName	: table
	 * @param attrs		: attributs à sélectionner
	 * @param where		: condition sur les enregistrements à sélectionner
	 * @param orderBys	: ordre(s) de sélection
	 * @throws SQLException
	 * @throws NoSuchFieldException : attribut non valide
	 */
	public ResultSet select(String tableName, LinkedList<String> attrs,
			Clause where, LinkedList<String> orderBys) throws SQLException,NoSuchFieldException {
		if ( tableName==null || tableName.length()==0)
			throw new NoSuchFieldException("Nom de table non valide");
		Statement stmt;
		try {
			stmt = this.createStatement();
		} catch (SQLException e) {
			throw e;
		}
		String query = "SELECT ";
		if ( attrs!=null && attrs.size()>0 ) {
			int c = attrs.size();
			Iterator<String> iter = attrs.iterator();
			while (iter.hasNext()) {
				c--;
				String attr = iter.next();
				Pattern pattern = Pattern.compile("(\\w*)"+"\\("+"(\\w*)"+"\\)");
				Matcher matcher = pattern.matcher(attr);
				if ( matcher.find() ) {
					String f = matcher.group(1);
					String a = matcher.group(2);
					pattern = Pattern.compile("`"+"(\\w*)"+"`");
					matcher = pattern.matcher(a);
					if (matcher.find()) {
						a = matcher.group(1);
					}
					query += f+"(`"+a+"`)";
				} else {
					query += "`" + attr + "`";
				}
				if (c > 0)
					query += ",";
			}
		} else {
			query += "*";
		}
		query += " FROM `" + tableName + "`";
		if (where != null) {
			query += " " + buildWhere(where);
		}
		if (orderBys!=null) {
			int obs = orderBys.size();
			String orderBy = "";
			for (int o=0; o<obs; o++) {
				String ob = orderBys.get(o);
				if (ob.charAt(0)=='-') orderBy += ", `"+ob.substring(1)+"` DESC";
				else orderBy += ", "+ob;
			}
			if (orderBy.length()>0) query += " ORDER BY"+orderBy.substring(1);
		}
		query += ";";
		System.out.println("Requete SQL : " + query);
		try {
			return stmt.executeQuery(query);
		} catch (SQLException e) {
			throw e;
		}
	}

	/**
	 * Methode de selection d'enregistrements dans la base de donnees
	 * @param table : nom de la table
	 * @param attrs : attributs a selectionner, null pour les selectionner tous
	 * @param where : clause de selection
	 */
	public ResultSet select(String tableName, LinkedList<String> attrs, Clause where)
			throws SQLException,NoSuchFieldException {
		return select(tableName, attrs, where, null);
	}

	/**
	 * Methode de selection d'enregistrements dans la base de donnees retournant tous les attributs
	 * 	Ne gère (pour l'instant) que les requêtes simples sans jointures
	 * @param table	: nom de la table
	 * @param attrs	: attributs a selectionner, null pour les selectionner tous
	 * @param where	: clause de selection
	 */
	public ResultSet select(String tableName, Clause where)
			throws SQLException,NoSuchFieldException {
		return select(tableName, null, where);
	}

	/**
	 * Methode de selection d'enregistrements dans la base de donnees sans condition
	 * 	Ne gère (pour l'instant) que les requêtes simples sans jointures
	 * @param table	: nom de la table
	 * @param attrs	: attributs a selectionner, null pour les selectionner tous
	 * @param where	: clause de selection
	 */
	public ResultSet select(String tableName, LinkedList<String> attrs)
			throws SQLException,NoSuchFieldException {
		return select(tableName, attrs, null);
	}

	/**
	 * Methode de selection d'enregistrements dans la base de donnees sans condition, retournant tous les attributs
	 * 	Ne gère (pour l'instant) que les requêtes simples sans jointures
	 * @param table	: nom de la table
	 * @param attrs	: attributs a selectionner, null pour les selectionner tous
	 * @param where	: clause de selection
	 */
	public ResultSet select(String tableName) throws SQLException,NoSuchFieldException {
		return select(tableName, null, null);
	}

	
	/**
	 * Méthode de modification
	 * @param tableName	: table à modifier
	 * @param values	: attributs à modifer et leurs nouvelles valeurs
	 * @param where		: condition sur les enregistrements
	 * @throws SQLException
	 * @throws NoSuchFieldException : attribut non valide
	 */
	public void update(String tableName, LinkedList<ValuedAttribute> values, Clause where)
		throws SQLException,NoSuchFieldException {
		if (tableName==null || tableName.length()==0)
			throw new NoSuchFieldException("Nom de table non valide");
		if ( values==null || values.size()==0 )
			throw new NoSuchFieldException("Valeurs non valides.");
		Statement stmt;
		try {
			stmt = this.createStatement();
		} catch (SQLException e) {
			throw e;
		}
		String query = "UPDATE `" + tableName + "`";
		if (values != null) {
			query += " SET ";
			int c = values.size();
			Iterator<ValuedAttribute> iter = values.iterator();
			while (iter.hasNext()) {
				ValuedAttribute a = (ValuedAttribute) iter.next();
				c--;
				query += "`" + a.getName() + "`" + "='" + a.getValue() + "'";
				if (c > 0)
					query += ",";
			}
		}
		if (where != null) {
			query += " " + buildWhere(where);
		}
		query += ";";
		System.out.println("Requete SQL : " + query);
		try {
			stmt.executeUpdate(query);
		} catch (SQLException e) {
			throw e;
		}
	}

	/**
	 * Méthode de modification (sans condition)
	 * @param tableName	: table à modifier
	 * @param values	: attributs à modifer et leurs nouvelles valeurs
	 * @throws SQLException
	 * @throws NoSuchFieldException : attribut non valide
	 */
	public void update(String tableName, LinkedList<ValuedAttribute> values)
			throws SQLException,NoSuchFieldException {
		this.update(tableName, values, null);
	}

	/**
	 * Méthode de suppression d'enregistrements
	 * @param tableName	: nom de la table
	 * @param where		: clause de sélection des enregistrements
	 * @throws SQLException
	 * @throws NoSuchFieldException : attribut non valide
	 */
	public void delete(String tableName, Clause where)
		throws SQLException,NoSuchFieldException {
		Statement stmt;
		if (tableName==null || tableName.length()==0)
			throw new NoSuchFieldException("Nom de table non valide");
		try {
			stmt = this.createStatement();
		} catch (SQLException e) {
			throw e;
		}
		String query = "DELETE ";
		query += "FROM `" + tableName + "`";
		if (where != null) {
			query += " " + buildWhere(where);
		}
		query += ";";
		System.out.println("Requete SQL : " + query);
		try {
			stmt.executeUpdate(query);
		} catch (SQLException e) {
			throw e;
		}
	}

}
